package dao

// @author: caoshi
// @date: 2021/5/22 15:07
import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"gradetable/src/domain"
	"gradetable/src/util"
)

//创建表
//func CreateTable(db *sql.DB) {
//	sql := `CREATE TABLE t_grade (
//			id VARCHAR ( 255 ) NOT NULL PRIMARY KEY,
//			stuName VARCHAR ( 255 ),
//			chinese INT ( 3 ),
//			math INT ( 3 ),
//			english INT ( 3 ),
//			physics INT ( 3 ),
//			chemistry INT ( 3 ),
//			biology INT ( 3 ),
//			total INT ( 4 ) )`
//
//	_, err := db.Exec(sql)
//	if err != nil {
//		fmt.Println(err)
//		fmt.Printf("执行失败!")
//	} else {
//		fmt.Printf("表创建成功!!")
//	}
//
//	defer func() {
//		if db != nil {
//			_ = db.Close()
//		}
//	}()
//
//}

func Insert(grade domain.Grade) {
	db, err := util.GetDB()
	defer func() {
		_ = db.Close()
	}()
	var sql = "insert into t_grade values (?, ?, ?, ?, ?, ?, ?, ?, ?)"
	stmt, _ := db.Prepare(sql)
	r, err := stmt.Exec(grade.Id, grade.StuName, grade.Chinese, grade.Math,
		grade.English, grade.Physics, grade.Chemistry, grade.Biology, grade.Total)
	if err != nil {
		fmt.Println("新增sql执行失败!")
		return
	}

	count, _ := r.RowsAffected()

	if count > 0 {
		fmt.Println("新增成绩数据成功!")
	} else {
		fmt.Println("新增成绩数据失败!")
	}

}

// 批量插入一堆数据
func BatchInsert(grades []domain.Grade) int {
	db, _ := util.GetDB()
	defer func() {
		_ = db.Close()
	}()
	var sqlStr = "insert into t_grade values (?, ?, ?, ?, ?, ?, ?, ?, ?)"
	stmt, _ := db.Prepare(sqlStr)
	var count = 0
	for _, item := range grades {
		// 这里执行sql时, 设置的参数个数要与sql语句中的占位符(?)的个数一致!
		res, err := stmt.Exec(item.Id, item.StuName, item.Chinese, item.Math, item.English,
			item.Physics, item.Chemistry, item.Biology, item.Total)
		if err != nil {
			fmt.Println("sql执行失败", err)
			continue
		}
		row, _ := res.RowsAffected()
		if row > 0 {
			count++
		}
	}

	fmt.Println("插入", count, "条数据!")
	return count
}

// 查询所有
func FindAll() []domain.Grade {
	db, _ := util.GetDB()
	defer func() {
		_ = db.Close()
	}()
	querySql := "select * from t_grade"
	var grades []domain.Grade
	err := db.Select(&grades, querySql)
	if err != nil {
		fmt.Println("查询失败", err)
		return nil
	}

	return grades
}
